package com.citywithincity.db.impl;

import com.citywithincity.api.ApiConfig;
import com.citywithincity.db.*;
import com.citywithincity.db.interfaces.Ar;
import com.citywithincity.db.interfaces.SqlDriver;
import com.citywithincity.utils.DataUtil;
import com.damai.utils.Caster;

import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

public class ArImpl extends SqlBuilder<Ar> implements Ar {
	private Record map;

	public ArImpl(SqlDriver s, Connection connection) {
		super(s, connection);
		map = new Record();
	}

	public void clear(boolean clear) {
		super.clear(clear);
		map.clear();
	}

	public void clear() {
		super.clear(true);
		map.clear();
	}

	public Ar set(String name, Object value) {
		map.put(name, value);
		return this;
	}

	@Override
	public Ar set(String value) {
		map.put(value, null);
		return this;
	}

	@Override
	public int insert(String tableName, String[] uniqueKeys) {
		// INSERT INTO customer (CM_ACC,CM_PWD)
		// SELECT '18659210057','123' FROM DUAL
		// WHERE NOT EXISTS (SELECT CM_ACC FROM customer WHERE
		// CM_ACC='18659210057')
		// INSERT INTO customer (CM_ACC,CM_PWD)
		// SELECT '18659210057','123' FROM DUAL
		// WHERE NOT EXISTS (SELECT CM_ACC FROM customer WHERE
		// CM_ACC='18659210057')
		//

		// String format = "INSERT INTO %s (%s) SELECT %s FROM DUAL WHERE NOT
		// EXISTS (SELECT %s from %s WHERE %s)";
		final Connection connection = this.connection;
		sql.append("INSERT INTO ").append(tableName).append(" (");
		StringBuilder tmp = new StringBuilder();
		boolean first = true;
		PreparedStatement ps = null;
		try {
			for (Entry<String, Object> entry : map.entrySet()) {
				Object value = entry.getValue();
				String name = entry.getKey();
				if (first) {
					first = false;
				} else {
					sql.append(',');
					tmp.append(',');
				}
				whereValues.add(value);
				sql.append(driver.protectColumn(name));// .append("=?");
				tmp.append('?');
			}
			sql.append(") SELECT ").append(tmp).append(" FROM DUAL WHERE NOT EXISTS ( SELECT 1 FROM ").append(tableName)
					// where
					.append(" WHERE ");
			first = true;
			for (String key : uniqueKeys) {
				if (first) {
					first = false;
				} else {
					sql.append(" AND ");
				}
				sql.append(key).append("=?");
			}

			sql.append(" ) ");
			ps = connection.prepareStatement(sql.toString());
			int index = setPs(ps);
			for (String key : uniqueKeys) {
				ps.setObject(index, map.get(key));
				++index;
			}
			int result = ps.executeUpdate();

			// 替换
			if (ApiConfig.debug()) {
				String s = sql.toString();
				s = s.replace("?", "'%s'");
				Object[] args = new Object[whereValues.size() + map.size()];

				index = 0;
				for (Object value : whereValues) {
					args[index++] = value;
				}

				for (String key : uniqueKeys) {
					args[index++] = map.get(key);
				}

				logger.info(String.format(s, args));
			}

			return result;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(ps);
		}
	}
	
	

	public int insert(String tableName) {
		sql.append("INSERT INTO ").append(tableName).append(" (");
		StringBuilder tmp = new StringBuilder();
		boolean first = true;
		PreparedStatement ps = null;
		try {
			for (Entry<String, Object> entry : map.entrySet()) {
				Object value = entry.getValue();
				String name = entry.getKey();
				if (first) {
					first = false;
				} else {
					sql.append(',');
					tmp.append(',');
				}
				whereValues.add(value);
				sql.append(driver.protectColumn(name));// .append("=?");
				tmp.append('?');
			}
			sql.append(") VALUES (").append(tmp).append(')');
			ps = connection.prepareStatement(sql.toString());
			setPs(ps);
			logSql();
			int result = ps.executeUpdate();
			return result;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(ps);
		}
	}

	public List<Record> get(String tableName) {
		buildSelect(tableName);
		return getList(sql.toString(), true);
	}

	public List<Record> getList(String sql) {
		return getList(sql, true);
	}

	@Override
	public void query(RecordVisitor visitor, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean exception = false;
		try {
			ps = connection.prepareStatement(sql);
			setPs(ps);
			rs = ps.executeQuery();
			if (ApiConfig.debug()) {
				logSql();
			}
			ArBuilder.visit(rs, visitor);
		} catch (SQLException e) {
			exception = true;
			throw new DBException(e);
		} finally {
			clear(exception);
			DbUtil.close(rs, ps);
		}
	}

	public void visit(String tableName, ResultSetVisitor visitor) {
		buildSelect(tableName);
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql.toString());
			setPs(ps);
			rs = ps.executeQuery();
			if (ApiConfig.debug()) {
				logSql();
			}
			ArBuilder.visit(rs, visitor);
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear(true);
			DbUtil.close(rs, ps);
		}
	}

	protected List<Record> getList(String sql, boolean clear) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean exception = false;
		try {
			ps = connection.prepareStatement(sql);
			setPs(ps);
			if (ApiConfig.debug()) {

				logSql();

			}
			rs = ps.executeQuery();

			return ArBuilder.build(rs);
		} catch (SQLException e) {
			exception = true;
			throw new DBException(be);
		} finally {
			clear(clear || exception);

			DbUtil.close(rs, ps);
		}
	}

	public Record getRow(String tableName) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			buildSelect(tableName, 0, 1);
			ps = connection.prepareStatement(sql.toString());
			setPs(ps);
			rs = ps.executeQuery();
			if (rs.next()) {
				return ArBuilder.buildOne(rs);
			}
			return null;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			if (ApiConfig.debug()) {
				logSql();
			}
			clear();
			DbUtil.close(rs, ps);
		}
	}

	public long insertGetId(String tableName, String pk) {
		sql.append("INSERT INTO ").append(tableName).append(" (");
		StringBuilder tmp = new StringBuilder();
		boolean first = true;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			for (Entry<String, Object> entry : map.entrySet()) {
				Object value = entry.getValue();
				String name = entry.getKey();
				if (first) {
					first = false;
				} else {
					sql.append(',');
					tmp.append(',');
				}
				whereValues.add(value);
				sql.append(name);// .append("=?");
				tmp.append('?');
			}
			sql.append(") VALUES (").append(tmp).append(')');
			ps = driver.prepareInsert(connection, sql.toString(), pk);
			setPs(ps);
			int result = ps.executeUpdate();
			if (result > 0) {
				return DataUtil.toLong(driver.getAutoIncrease(ps));
			}
			return 0;
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(rs, ps);
		}
	}

	public String genUpdate(String tableName) {
		sql.append("UPDATE ").append(tableName);

		boolean first = true;
		int index = 0;
		for (Entry<String, Object> entry : map.entrySet()) {
			Object value = entry.getValue();
			if (first) {
				first = false;
				sql.append(" SET ");
			} else {
				sql.append(',');
			}
			if (value == null) {
				sql.append(entry.getKey());
			} else {
				whereValues.add(index++, value);
				sql.append(entry.getKey()).append("=?");
			}

		}

		sql.append(where);

		String s = sql.toString();
		s = s.replace("?", "'%s'");
		Object[] args = new Object[whereValues.size()];

		index = 0;
		for (Object value : whereValues) {
			args[index++] = value;
		}

		return (String.format(s, args));
	}

	public int update(String tableName) {
		PreparedStatement ps = null;
		try {
			sql.append("UPDATE ").append(tableName);

			boolean first = true;
			int index = 0;
			for (Entry<String, Object> entry : map.entrySet()) {
				Object value = entry.getValue();
				if (first) {
					first = false;
					sql.append(" SET ");
				} else {
					sql.append(',');
				}
				if (value == null) {
					sql.append(entry.getKey());
				} else {
					whereValues.add(index++, value);
					sql.append(entry.getKey()).append("=?");
				}

			}

			sql.append(where);
			ps = connection.prepareStatement(sql.toString());
			setPs(ps);
			if (ApiConfig.debug()) {
				logSql();
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			;
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(ps);
		}
	}

	public int delete(String tableName) {
		if (where.length() <= 0) {
			throw new DBException("Whole table delete is not valid!");
		}
		sql.append("DELETE FROM ").append(tableName).append(where);
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql.toString());
			setPs(ps);
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(ps);
		}
	}

	public Page<Record> page(String tableName, int page, int pageSize) {
		Page<Record> p = new Page<Record>();
		p.setPage(page);
		p.setPageSize(pageSize);
		p.setList(get(tableName, page, pageSize, false));
		p.setTotalRows(getRows(tableName));
		p.setTotalPages((int) Math.ceil((double) p.getTotalRows() / p.getPageSize()));
		return p;
	}

	public List<Record> get(String tableName, int page, int pageSize) {
		return get(tableName, page, pageSize, true);
	}

	public List<Record> get(String tableName, int page, int pageSize, boolean clear) {
		if (page == 0)
			page = 1;
		buildSelect(tableName, (page - 1) * pageSize, pageSize);
		return getList(sql.toString(), clear);
	}

	public Page<Record> position(String tableName, int position, int pageSize) {
		buildSelect(tableName, position, pageSize);
		Page<Record> p = new Page<Record>();
		p.setPage(driver.posToPage(position, pageSize));
		p.setPageSize(pageSize);
		p.setList(getList(sql.toString(), false));
		p.setTotalRows(getRows(tableName));
		p.setTotalPages((int) Math.ceil((double) p.getTotalRows() / p.getPageSize()));
		return p;
	}

	@Override
	public List<Record> limit(String tableName, int position, int pageSize) {
		buildSelect(tableName, position, pageSize);
		return getList(sql.toString(), true);
	}

	public Ar syncTo(Record object) {
		object.putAll(map);
		return this;
	}

	@Override
	public List<Record> getSql(String sql) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			return ArBuilder.build(rs);
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear(true);
			DbUtil.close(rs, ps);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> T getValue(String table, String columnName) {
		Record map = select(columnName).getRow(table);
		if (map == null)
			return null;
		return (T) map.get(columnName);
	}

	@Override
	public <T> T getValue(String table, String columnName, Class<T> clazz) {
		Record map = select(columnName).getRow(table);
		if (map == null)
			return Caster.to(null, clazz);
		if(columnName.contains("AS")){
			columnName = columnName.substring( columnName.indexOf("AS") + 3  );
		}
		return Caster.to(map.get(columnName), clazz);
	}

	@Override
	public Ar set(Map<String, ?> map) {
		for (Entry<String, ?> entry : map.entrySet()) {
			set(entry.getKey(), entry.getValue());
		}
		return this;
	}

	@Override
	public long selectMax(String table, String field) {
		select(new StringBuilder(" MAX(").append(field).append(") AS ").append(field).toString());
		Record map = getRow(table);
		Object value = map.get(field);
		if (value == null) {
			return 0;
		}
		return DataUtil.toLong(value);
	}

	@Override
	public String selectMaxString(String table, String field) {
		select(new StringBuilder(" MAX(").append(field).append(") AS ").append(field).toString());
		Record map = getRow(table);
		Object value = map.get(field);
		if (value == null)
			return null;
		return String.valueOf(value);
	}

	@Override
	public Ar from(String tableName) {
		sql.append(" FROM ").append(tableName);
		return this;
	}

	@Override
	public long selectSum(String table, String field) {
		select(new StringBuilder(" SUM(").append(field).append(") AS ").append(field).toString());
		Record map = getRow(table);
		Object value = map.get(field);
		if (value == null) {
			return 0;
		}
		return DataUtil.toLong(value);
	}

	@Override
	public int insertOrUpdate(String tableName, String keyName) {
		return getDriver().insertOrUpdate(this, tableName, keyName, this.map);
	}

	@Override
	public int insertOrUpdate(String tableName, String[] unikeys) {
		return getDriver().insertOrUpdate(this, tableName, unikeys, this.map);
	}

	@Override
	public List<Record> query(String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			setPs(ps, args);
			rs = ps.executeQuery();
			return ArBuilder.build(rs);
		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(rs, ps);
		}
	}

	@SuppressWarnings("unused")
	@Override
	public void call(String processName, Object... args) {

		CallableStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append("{call ").append(processName).append("(");
			boolean first = true;
			for (Object object : args) {
				if (first) {
					first = false;
				} else {
					sb.append(",");
				}
				sb.append("?");
			}
			sb.append(")}");
			ps = connection.prepareCall(sb.toString());
			int index = 1;
			HashSet<Integer> sets = new HashSet<Integer>();
			for (Object object : args) {
				if (object instanceof DbOutParam) {
					DbOutParam param = (DbOutParam) object;
					ps.registerOutParameter(index, param.getType());
					sets.add(index);
				} else {
					ps.setObject(index, (String) object);
				}

				++index;
			}
			ps.execute();

			for (Integer callIndex : sets) {
				int cIndex = callIndex;
				Object value = ps.getObject(cIndex);
				DbOutParam param = (DbOutParam) args[cIndex - 1];
				param.setValue(value);
			}

		} catch (SQLException e) {
			throw new DBException(e);
		} finally {
			clear();
			DbUtil.close(rs, ps);
		}

	}

	@Override
	public PageVisitor<Record> startVisitPage(String tableName, int pageSize) {
		Page<Record> page = position(tableName, 0, pageSize);
		if (!page.isLast()) {
			for (int i = 1, c = page.getTotalPages(); i < c; ++i) {

			}
		}

		return null;
	}

}
